class CreateAlarmsView < ActiveRecord::Migration
  def self.up
    execute "create view alarms as select
    obj1.name1 as host_name,
    obj1.name2 as service,
    n.service_object_id as object_id,
    n.current_state as status,
    n.last_check as last_check,
    n.last_state_change as duration,
    n.current_check_attempt,
    n.max_check_attempts,
    n.output as status_information,
    n.passive_checks_enabled as passive
    from ndo.nagios_servicestatus as n
    left join ndo.nagios_objects as obj1 on n.service_object_id=obj1.object_id
    left join ndo.nagios_services on n.service_object_id=ndo.nagios_services.service_object_id
    left join ndo.nagios_instances on n.instance_id=ndo.nagios_instances.instance_id
    where ndo.nagios_services.config_type='1'
    and n.problem_has_been_acknowledged=0 and n.scheduled_downtime_depth=0
    and obj1.name1 not in ( select obj1.name1
    from  ndo.nagios_hoststatus as nhs
    left join ndo.nagios_objects as obj1 on nhs.host_object_id=obj1.object_id
    left join ndo.nagios_hosts ON nhs.host_object_id=ndo.nagios_hosts.host_object_id
    left join ndo.nagios_instances ON ndo.nagios_hosts.instance_id=ndo.nagios_instances.instance_id
    where ndo.nagios_hosts.config_type='1' and (nhs.scheduled_downtime_depth>0 OR nhs.problem_has_been_acknowledged>0))
    order by last_state_change desc, host_name asc, service asc
    "
  end

  def self.down
    execute "drop view alarms"
  end
end
